package com.gxuwz.medical.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.gxuwz.medical.database.DbUtil;
import com.gxuwz.medical.domain.area.*;
import com.gxuwz.medical.vo.PageBean;

public class AreaDao extends GenericDao<Area>{
	@Override
	protected Area handle(ResultSet rs) throws SQLException {
		Area a = new Area();
		a.setAreacode(rs.getString("areacode"));
		a.setAreapcode(rs.getString("areapcode"));
		a.setAreaname(rs.getString("areaname"));
		a.setArealevel(rs.getInt("arealevel"));
		return a;
	}
	
	public List queryByLevel(int level) throws SQLException{
		String sql ="select * from area where arealevel=?";
		Object[] params={level};
		return super.queryAll(sql, params);
	}
	public List getChildren(String areapcode) throws SQLException{
		String sql ="select * from area where areapcode=?";
		Object[] params={areapcode};
		return super.queryAll(sql, params);
	}
	
	public void add(Area a) throws SQLException {
		try {
			DbUtil db = new DbUtil();
			String sql = "insert into area(areacode,areapcode,areaname,arealevel) values('"
					+ a.getAreacode() + "','" + a.getAreapcode() + "','" + a.getAreaname() + "','" + a.getArealevel() + "')";
			db.executeUpdate(sql);
		} catch (SQLException e) {
			throw new SQLException("添加行政区域失败 " + e.getMessage(), e);
		}
	}

	public void delete(String areacode) throws SQLException {
		try {
			DbUtil db = new DbUtil();
			String sql = "delete from area where areacode='" + areacode + "'";
			db.executeUpdate(sql);
		} catch (SQLException e) {
			throw new SQLException("删除行政区域失败" + e.getMessage(), e);
		}
	}

	public Area query(String areacode) throws SQLException {
		Area a = new Area();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select * from area where areacode='" + areacode
					+ "'";
			rs = db.executeQuery(stmt, sql);
			if (rs.next()) {
				a=handle(rs);
			}

		} catch (SQLException e) {
			throw new SQLException("查询行政区域失败" + e.getMessage(), e);
		}finally{
			DbUtil.close(rs, stmt, conn);
		}
		return a;
	}
	
	public int count() throws SQLException{//返回记录数
		int count = 0;
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select count(areacode) from area";
			rs = stmt.executeQuery(sql);
			if(rs.next())
				count = rs.getInt(1);
		}catch (SQLException e) {
			throw new SQLException("查询记录数量失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
		return count;
	}

	public PageBean queryAll(int page,int size) throws SQLException {
		PageBean p = new PageBean(page,size,count());
		List list = new ArrayList();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select * from area limit "+(page-1)*size+","+size+"";
			rs = db.executeQuery(stmt, sql);
			while (rs.next()) {
				Area a = new Area();
				a=handle(rs);
				list.add(a);
			}
			p.setPageData(list);
			return p;

		} catch (SQLException e) {
			throw new SQLException("查询全部行政区域失败" + e.getMessage(), e);
		}finally{
			DbUtil.close(rs, stmt, conn);
		}
	}
	
	public List<Area> queryAll() throws SQLException {
		List<Area> list = new ArrayList<Area>();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select * from area";
			rs = db.executeQuery(stmt, sql);
			while (rs.next()) {
				Area a = new Area();
				a=handle(rs);
				list.add(a);
			}
			
			return list;

		} catch (SQLException e) {
			throw new SQLException("查询全部行政区域失败" + e.getMessage(), e);
		}finally{
			DbUtil.close(rs, stmt, conn);
		}
	}

	public void update(Area a) throws SQLException {
		try {
			DbUtil db = new DbUtil();
			String sql = "update area set areaname='"+a.getAreaname()+"' where areacode='"+a.getAreacode()+"'";
			db.executeUpdate(sql);
		} catch (SQLException e) {
			throw new SQLException("修改行政区域失败 " + e.getMessage(), e);
		}
	}

	
	
}
